<?php
require_once ($_SERVER['DOCUMENT_ROOT'].'/global.php');
include_once ($_SERVER['DOCUMENT_ROOT'].'/PHPExcel/Classes/PHPExcel.php');
$objPHPExcel = new PHPExcel();
/*以下是一些设置 ，什么作者  标题啊之类的*/
$objPHPExcel->getProperties()->setCreator("www.evuan.com")
->setLastModifiedBy('DingJinBiao')
->setTitle("DingJinBiao")
->setSubject("DingJinBiao")
->setDescription("DingJinBiao")
->setKeywords("DingJinBiao")
->setCategory("DingJinBiao");
/////////////////////////////////////////////
$array = array();

function getColumnNumber($char){//根据列号获取列对应数字
	$varchar = 'A';
	for ($i=0; $i < 10000; $i++) {
		if ($varchar == $char) {
			break;
		}
		$varchar++;
	}
	return $i;
}
function get_char($char,$n){
	for ($i=0; $i < $n; $i++) {
		$char++;
	}
	return $char;
}
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(0)->setTitle('门禁报表')
  ->setCellValue('A1', '序号')
  ->setCellValue('B1', '姓名')
  ->setCellValue('C1', '学工号')
  ->setCellValue('D1', '读者类型');
//统计日期
$sql = " select RiQi from entry_record_auto group by RiQi order by RiQi ";
$query = $mysqli->query($sql);
$RiQiArray = array();
$counter = 0;
while ($a = $query->fetch_assoc()) {
  $RiQiArray[] = $a;
}
$RiQiSql = "";
// for ($i = 0; $i < count($RiQiArray); $i++) {
//   $objPHPExcel->setActiveSheetIndex(0)
//     ->setCellValue(get_char('A',$i+4).'1',$RiQiArray[$i]['RiQi']);
//   $j = $i+1;
//   $RiQiSql .= "0+cast(round(sum(if(RiQi = '{$RiQiArray[$i]['RiQi']}',Duration,0)),1) as char) RiQi{$j},";
// }
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('E'.'1','合计(小时)');
// ->setCellValue(get_char('A',$i+4).'1','合计(小时)');

$sql = "
  SELECT
    {$RiQiSql}
    0+cast(round(sum(Duration),1) as char) Duration,
    b.XingMing,
    b.XueHao,
    if(LeiBie=1,'教师','学生') LeiBie
  FROM
    entry_record_auto a,
    user b
  WHERE
    a.XueHao = b.XueHao
  group by a.XueHao
  order by
    a.XueHao+0
";
$query = $mysqli->query($sql);
$jishuqi = 1;
while ($a = $query->fetch_assoc()) {
  $objPHPExcel->setActiveSheetIndex(0)
              ->setCellValue('A'.(string)($jishuqi+1),$jishuqi)
              ->setCellValue('B'.(string)($jishuqi+1),$a['XingMing'])
              ->setCellValue('C'.(string)($jishuqi+1),$a['XueHao'])
              ->setCellValue('D'.(string)($jishuqi+1),$a['LeiBie']) ;
  // for ($i = 0; $i < count($RiQiArray); $i++) {
  //     $j = $i+1;
  //       $objPHPExcel->setActiveSheetIndex(0)
  //           ->setCellValue(get_char('A',$i+4).(string)($jishuqi+1),$a['RiQi'.$j]) ;
  // }
  $objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('E'.(string)($jishuqi+1),$a['Duration']) ;
    // ->setCellValue(get_char('A',$i+4).(string)($jishuqi+1),$a['Duration']) ;
  $jishuqi++;
}
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1)->setTitle('门禁统计')
  ->setCellValue('A1', '年度')
  ->setCellValue('B1', '日期')
  ->setCellValue('C1', '教师入馆人次')
  ->setCellValue('D1', '较上周')
  ->setCellValue('E1', '教师入馆时长')
  ->setCellValue('F1', '较上周')
  ->setCellValue('G1', '学生入馆人次')
  ->setCellValue('H1', '较上周')
  ->setCellValue('I1', '学生入馆时长')
  ->setCellValue('J1', '较上周')
  ->setCellValue('K1', '教师借书')
  ->setCellValue('L1', '较上周')
  ->setCellValue('M1', '学生借书')
  ->setCellValue('N1', '较上周');
//入馆人数
$sql = "
SELECT
    count(b.LeiBie = 1 or null) teacher_in_count,
    count(b.LeiBie <> 1 or null) student_in_count
FROM
    entry_ori_record a,
    user b
WHERE
    a.xuehao = b.xuehao
";
$query = $mysqli->query($sql);
$result = $query->fetch_assoc();
$teacher_in_count = $result['teacher_in_count'];
$student_in_count = $result['student_in_count'];
//教师入馆时长
$sql = "
SELECT
    round(sum(duration),1) teacher_in_duration
FROM
    entry_record_auto a,
    user b
WHERE
    a.xuehao = b.xuehao
    and b.LeiBie = 1
";
$query = $mysqli->query($sql);
$result = $query->fetch_assoc();
$teacher_in_duration = $result['teacher_in_duration'];
//学生入馆时长
$sql = "
SELECT
    round(sum(duration),1) student_in_duration
FROM
    entry_record_auto a,
    user b
WHERE
    a.xuehao = b.xuehao
    and b.LeiBie <> 1
";
$query = $mysqli->query($sql);
$result = $query->fetch_assoc();
$student_in_duration = $result['student_in_duration'];
//教师借书
$sql = "
SELECT
    sum(ce) teacher_lend
FROM
    entry_lend_record a,
    user b
WHERE
    a.xuehao = b.xuehao
    and b.LeiBie = 1
";
$query = $mysqli->query($sql);
$result = $query->fetch_assoc();
$teacher_lend = $result['teacher_lend'];
//学生借书
$sql = "
SELECT
    sum(ce) student_lend
FROM
    entry_lend_record a,
    user b
WHERE
    a.xuehao = b.xuehao
    and b.LeiBie <> 1
";
$query = $mysqli->query($sql);
$result = $query->fetch_assoc();
$student_lend = $result['student_lend'];
$objPHPExcel
    ->setActiveSheetIndex(1)
    ->setCellValue('C2',$teacher_in_count)
    ->setCellValue('E2',$teacher_in_duration)
    ->setCellValue('G2',$student_in_count)
    ->setCellValue('I2',$student_in_duration)
    ->setCellValue('K2',$teacher_lend)
    ->setCellValue('M2',$student_lend);

// $objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(2)->setTitle('教师二级学院数据')
  ->setCellValue('A1', '学院')
  ->setCellValue('B1', '入馆人次')
  ->setCellValue('C1', '入馆时长')
  ->setCellValue('D1', '借阅册数');
$sql = "
SELECT
    a.XueYuan,
    ifnull(b.renci,0) renci,
    ifnull(c.shichang,0) shichang,
    ifnull(d.Ce,0) Ce
FROM
user a
left join (
  select
    a.XueYuan,
    count(1) renci
  from
    user a,
    entry_ori_record b
  where
    a.xuehao = b.xuehao
    and a.XueYuan <> ''
  group by
    a.XueYuan
) b on (a.XueYuan = b.XueYuan)
left join (
  select
    a.XueYuan,
    round(sum(b.duration),1) shichang
  from
    user a,
    entry_record_auto b
  where
    a.xuehao = b.xuehao
    and a.XueYuan <> ''
  group by
    a.XueYuan
) c on (a.XueYuan = c.XueYuan)
left join (
  select
    a.XueYuan,
    sum(b.ce) Ce
  from
    user a,
    entry_lend_record b
  where
    a.xuehao = b.xuehao
    and a.XueYuan <> ''
  group by
    a.XueYuan
) d on (a.XueYuan = d.XueYuan)
WHERE
    a.LeiBie = 1
    and a.XueYuan <> ''
group by
    a.XueYuan
";
$query = $mysqli->query($sql);
$jishuqi = 1;
while ($a = $query->fetch_assoc()) {
  $objPHPExcel->setActiveSheetIndex(2)
    ->setCellValue('A'.(string)($jishuqi+1),$a['XueYuan'])
    ->setCellValue('B'.(string)($jishuqi+1),$a['renci'])
    ->setCellValue('C'.(string)($jishuqi+1),$a['shichang'])
    ->setCellValue('D'.(string)($jishuqi+1),$a['Ce']) ;
  $jishuqi++;
}
//--------------------------------------------------
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(3)->setTitle('学生二级学院入馆数据')
  ->setCellValue('A1', '学院')
  ->setCellValue('B1', '人数')
  ->setCellValue('C1', '入馆人次')
  ->setCellValue('D1', '人均入馆次数')
  ->setCellValue('E1', '入馆时长(小时)')
  ->setCellValue('F1', '人均入馆时长(小时)')
;
$sql = "
SELECT
    a.XueYuan,
    a.renshu,
    ifnull(b.renci,0) renci,
    round(ifnull(b.renci,0)/a.renshu,2) renjun_renci,
    ifnull(c.shichang,0) shichang,
    round(ifnull(c.shichang,0)/a.renshu,2) renjun_shichang
FROM
    (SELECT
        a.xueyuan,
        count(1) renshu
    FROM `user` a
    WHERE
        a.leibie = 2
        and a.xuehao >= '2021'
        and a.xueyuan <> ''
    group by
        a.xueyuan) a
left join (
    select
        a.XueYuan,
        count(1) renci
    from
        user a,
        entry_ori_record b
    where
        a.xuehao = b.xuehao
        and a.XueYuan <> ''
    group by
        a.XueYuan
    ) b on (a.XueYuan = b.XueYuan)
left join (
    select
        a.XueYuan,
        round(sum(b.duration),1) shichang
    from
        user a,
        entry_record_auto b
    where
        a.xuehao = b.xuehao
        and a.leibie = 2
        and a.xuehao >= '2021'
        and a.XueYuan <> ''
    group by
        a.XueYuan
    ) c on (a.XueYuan = c.XueYuan)
WHERE
    1
";
$query = $mysqli->query($sql);
$jishuqi = 1;
while ($a = $query->fetch_assoc()) {
  $objPHPExcel->setActiveSheetIndex(3)
    ->setCellValue('A'.(string)($jishuqi+1),$a['XueYuan'])
    ->setCellValue('B'.(string)($jishuqi+1),$a['renshu'])
    ->setCellValue('C'.(string)($jishuqi+1),$a['renci'])
    ->setCellValue('D'.(string)($jishuqi+1),$a['renjun_renci'])
    ->setCellValue('E'.(string)($jishuqi+1),$a['shichang'])
    ->setCellValue('F'.(string)($jishuqi+1),$a['renjun_shichang']);
  $jishuqi++;
}

$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="汇总表.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>
